import logging
import akshare as ak
import pymysql

from sshtunnel import SSHTunnelForwarder

logging.basicConfig(level=logging.INFO,
                    format='%(asctime)s | %(filename)s - {%(lineno)d} - [%(levelname)s] - %(message)s',
                    datefmt='%Y-%m-%d %H:%M:%S', encoding='utf-8')

ssh_server = SSHTunnelForwarder(
    ssh_address_or_host=("39.99.32.167", 22),
    ssh_username="root",
    ssh_password="uYNDTLpD7QBA3RpRyXjQyqJB7pH@",
    remote_bind_address=("192.168.0.2", 3306),
)

ssh_server.start()
logging.info("ssh tunel create successful at: {} !!!".format(ssh_server.local_bind_port))

connect = pymysql.connect(host='127.0.0.1', port=ssh_server.local_bind_port, user='root', passwd='Ajt+2020@123P',
                          database='stock')
cursor = connect.cursor()

stock_list = ak.stock_zh_a_spot_em()
size = len(stock_list)
for idx in range(size):
    item = stock_list.loc[idx]
    stock_code = item["代码"]
    stock_name = item["名称"]
    select_sql = "select count(1) from stock_code where stock_code='%s'" % stock_code
    cursor.execute(select_sql)
    current_select_count = cursor.fetchone()[0]
    if current_select_count == 0:
        insert_sql = "insert into stock_code values ('%s','%s')" % (stock_code, stock_name)
        cursor.execute(insert_sql)
        connect.commit()
        logging.info("code: {}, name: {}".format(item["代码"], item["名称"]))
    else:
        logging.info("stock_code: {} exists in database".format(stock_code))

cursor.close()
connect.close()

ssh_server.stop()
